
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/07/2015 17:59:50
-- Generated from EDMX file: D:\work\GBH\GBH\GBH\Models\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [GBH];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Branch]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Branch];
GO
IF OBJECT_ID(N'[dbo].[daysellMinMax]', 'U') IS NOT NULL
    DROP TABLE [dbo].[daysellMinMax];
GO
IF OBJECT_ID(N'[dbo].[draftPi]', 'U') IS NOT NULL
    DROP TABLE [dbo].[draftPi];
GO
IF OBJECT_ID(N'[dbo].[ExportPi]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ExportPi];
GO
IF OBJECT_ID(N'[dbo].[ExportPiDetail]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ExportPiDetail];
GO
IF OBJECT_ID(N'[dbo].[FileUploadHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FileUploadHistory];
GO
IF OBJECT_ID(N'[dbo].[location]', 'U') IS NOT NULL
    DROP TABLE [dbo].[location];
GO
IF OBJECT_ID(N'[dbo].[material]', 'U') IS NOT NULL
    DROP TABLE [dbo].[material];
GO
IF OBJECT_ID(N'[dbo].[MaterialLocation]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MaterialLocation];
GO
IF OBJECT_ID(N'[dbo].[productIdMapping]', 'U') IS NOT NULL
    DROP TABLE [dbo].[productIdMapping];
GO
IF OBJECT_ID(N'[dbo].[role]', 'U') IS NOT NULL
    DROP TABLE [dbo].[role];
GO
IF OBJECT_ID(N'[dbo].[sell_out]', 'U') IS NOT NULL
    DROP TABLE [dbo].[sell_out];
GO
IF OBJECT_ID(N'[dbo].[stock]', 'U') IS NOT NULL
    DROP TABLE [dbo].[stock];
GO
IF OBJECT_ID(N'[dbo].[unit_type]', 'U') IS NOT NULL
    DROP TABLE [dbo].[unit_type];
GO
IF OBJECT_ID(N'[dbo].[user]', 'U') IS NOT NULL
    DROP TABLE [dbo].[user];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Branches'
CREATE TABLE [dbo].[Branches] (
    [id] int IDENTITY(1,1) NOT NULL,
    [mbranchno] char(5)  NOT NULL,
    [mcustno] char(10)  NOT NULL,
    [mprovice] char(2)  NOT NULL,
    [mprovdesc] nvarchar(1000)  NULL,
    [mprovtrans] char(4)  NULL,
    [mprovtransdesc] nvarchar(1000)  NULL,
    [mprovtran_grp] int  NULL,
    [isenable] bit  NOT NULL
);
GO

-- Creating table 'daysellMinMaxes'
CREATE TABLE [dbo].[daysellMinMaxes] (
    [product_id] nvarchar(18)  NOT NULL,
    [location_id] char(3)  NOT NULL,
    [min] int  NULL,
    [max] int  NULL,
    [rop] int  NULL,
    [isEnable] bit  NOT NULL,
    [updateDate] datetime  NULL
);
GO

-- Creating table 'FileUploadHistories'
CREATE TABLE [dbo].[FileUploadHistories] (
    [id] int IDENTITY(1,1) NOT NULL,
    [Filename] nvarchar(200)  NOT NULL,
    [FilePath] nvarchar(1000)  NOT NULL,
    [DateTime] datetime  NULL,
    [NumberOfRow] int  NOT NULL,
    [UploadType] int  NOT NULL
);
GO

-- Creating table 'locations'
CREATE TABLE [dbo].[locations] (
    [id] char(3)  NOT NULL,
    [name] nvarchar(200)  NOT NULL,
    [isEnable] bit  NULL
);
GO

-- Creating table 'materials'
CREATE TABLE [dbo].[materials] (
    [mmatno] nvarchar(18)  NOT NULL,
    [sizeunit] nvarchar(500)  NULL,
    [setunit] nvarchar(500)  NULL,
    [quantitypersetunit] int  NULL,
    [packageunit] nvarchar(500)  NULL,
    [setunitsize] nvarchar(200)  NULL,
    [isEnable] bit  NULL,
    [npibarcode] varchar(18)  NULL,
    [mnamethai] nvarchar(100)  NULL
);
GO

-- Creating table 'productIdMappings'
CREATE TABLE [dbo].[productIdMappings] (
    [gbhProductId] nvarchar(18)  NOT NULL,
    [nawaProductId] nvarchar(18)  NOT NULL,
    [description] nvarchar(max)  NULL,
    [isEnable] bit  NULL
);
GO

-- Creating table 'roles'
CREATE TABLE [dbo].[roles] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'sell_out'
CREATE TABLE [dbo].[sell_out] (
    [id] int IDENTITY(1,1) NOT NULL,
    [product_id] nvarchar(18)  NOT NULL,
    [location_id] char(3)  NOT NULL,
    [qty] int  NULL,
    [unit_type_name] nvarchar(50)  NULL,
    [value] decimal(18,2)  NULL,
    [isEnable] bit  NOT NULL,
    [date_time] datetime  NULL,
    [product_name] nvarchar(1000)  NULL
);
GO

-- Creating table 'stocks'
CREATE TABLE [dbo].[stocks] (
    [id] int IDENTITY(1,1) NOT NULL,
    [product_id] nvarchar(18)  NOT NULL,
    [location_id] char(3)  NOT NULL,
    [qty] int  NULL,
    [unit_type_name] nvarchar(50)  NOT NULL,
    [value] decimal(18,2)  NULL,
    [isEnable] bit  NULL,
    [product_name] nvarchar(1000)  NULL
);
GO

-- Creating table 'unit_type'
CREATE TABLE [dbo].[unit_type] (
    [name] nvarchar(50)  NOT NULL,
    [isEnable] bit  NULL
);
GO

-- Creating table 'users'
CREATE TABLE [dbo].[users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(50)  NOT NULL,
    [Password] nvarchar(50)  NOT NULL,
    [Email] nvarchar(50)  NOT NULL,
    [FullName] nvarchar(100)  NOT NULL,
    [Role] int  NOT NULL,
    [Telephone] nvarchar(50)  NULL,
    [Fax] nvarchar(50)  NULL,
    [PinCode] nvarchar(4)  NULL,
    [Active] bit  NOT NULL
);
GO

-- Creating table 'MaterialLocations'
CREATE TABLE [dbo].[MaterialLocations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [MatNo] varchar(18)  NOT NULL,
    [LocationNo] char(5)  NOT NULL,
    [IsEnable] bit  NOT NULL
);
GO

-- Creating table 'draftPis'
CREATE TABLE [dbo].[draftPis] (
    [id] int IDENTITY(1,1) NOT NULL,
    [branchCode] char(4)  NOT NULL,
    [productId] nvarchar(18)  NOT NULL,
    [productName] nvarchar(1000)  NULL,
    [nawaProductId] nvarchar(18)  NOT NULL,
    [nawaProductName] nvarchar(1000)  NULL,
    [type] nvarchar(10)  NULL,
    [qty] int  NOT NULL,
    [isEnable] bit  NOT NULL,
    [isDraft] bit  NOT NULL
);
GO

-- Creating table 'ExportPis'
CREATE TABLE [dbo].[ExportPis] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BranchCode] char(4)  NOT NULL,
    [BranchName] nvarchar(1000)  NOT NULL,
    [FileName] nvarchar(1000)  NOT NULL,
    [ExportDate] datetime  NULL,
    [CreateDate] datetime  NOT NULL,
    [ExportVersion] int  NULL
);
GO

-- Creating table 'ExportPiDetails'
CREATE TABLE [dbo].[ExportPiDetails] (
    [id] int IDENTITY(1,1) NOT NULL,
    [ExportId] int  NOT NULL,
    [branchCode] char(4)  NOT NULL,
    [productId] nvarchar(18)  NOT NULL,
    [productName] nvarchar(1000)  NULL,
    [nawaProductId] nvarchar(18)  NOT NULL,
    [nawaProductName] nvarchar(1000)  NULL,
    [type] nvarchar(10)  NULL,
    [qty] int  NOT NULL,
    [filename] nvarchar(1000)  NULL,
    [isEnable] bit  NOT NULL,
    [isExport] bit  NOT NULL,
    [createDate] datetime  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [id] in table 'Branches'
ALTER TABLE [dbo].[Branches]
ADD CONSTRAINT [PK_Branches]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [product_id], [location_id] in table 'daysellMinMaxes'
ALTER TABLE [dbo].[daysellMinMaxes]
ADD CONSTRAINT [PK_daysellMinMaxes]
    PRIMARY KEY CLUSTERED ([product_id], [location_id] ASC);
GO

-- Creating primary key on [id] in table 'FileUploadHistories'
ALTER TABLE [dbo].[FileUploadHistories]
ADD CONSTRAINT [PK_FileUploadHistories]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'locations'
ALTER TABLE [dbo].[locations]
ADD CONSTRAINT [PK_locations]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [mmatno] in table 'materials'
ALTER TABLE [dbo].[materials]
ADD CONSTRAINT [PK_materials]
    PRIMARY KEY CLUSTERED ([mmatno] ASC);
GO

-- Creating primary key on [gbhProductId], [nawaProductId] in table 'productIdMappings'
ALTER TABLE [dbo].[productIdMappings]
ADD CONSTRAINT [PK_productIdMappings]
    PRIMARY KEY CLUSTERED ([gbhProductId], [nawaProductId] ASC);
GO

-- Creating primary key on [Id] in table 'roles'
ALTER TABLE [dbo].[roles]
ADD CONSTRAINT [PK_roles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [id] in table 'sell_out'
ALTER TABLE [dbo].[sell_out]
ADD CONSTRAINT [PK_sell_out]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'stocks'
ALTER TABLE [dbo].[stocks]
ADD CONSTRAINT [PK_stocks]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [name] in table 'unit_type'
ALTER TABLE [dbo].[unit_type]
ADD CONSTRAINT [PK_unit_type]
    PRIMARY KEY CLUSTERED ([name] ASC);
GO

-- Creating primary key on [Id] in table 'users'
ALTER TABLE [dbo].[users]
ADD CONSTRAINT [PK_users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MaterialLocations'
ALTER TABLE [dbo].[MaterialLocations]
ADD CONSTRAINT [PK_MaterialLocations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [id] in table 'draftPis'
ALTER TABLE [dbo].[draftPis]
ADD CONSTRAINT [PK_draftPis]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [Id] in table 'ExportPis'
ALTER TABLE [dbo].[ExportPis]
ADD CONSTRAINT [PK_ExportPis]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [id] in table 'ExportPiDetails'
ALTER TABLE [dbo].[ExportPiDetails]
ADD CONSTRAINT [PK_ExportPiDetails]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------